﻿using System.Data;
using Npgsql;
using NpgsqlTypes;
using PureMVC.Interfaces;
using PureMVC.Patterns;
using SMS.Core.Model.Data;

namespace SMS.Core.Model.Item
{
    public class ItemProxy : Proxy, IProxy
    {
        #region Constructors

        public ItemProxy(NpgsqlConnection conn)
        {
            m_conn = conn;
            m_dataAdapter = new NpgsqlDataAdapter(QRY_ADAPTER_SELECT, conn);
            m_dataAdapter.Fill(AppDataCache.Instance.Data.staff);
            InitializeDataAdapter();
            InitializeEventHandlers();
        }

        #endregion

        #region Members

        private NpgsqlConnection m_conn;

        #region Static Members

        private static object m_staticSyncRoot = new object();
        private static NpgsqlDataAdapter m_dataAdapter;

        #endregion

        #region Properties

        public new AppData.itemDataTable Data
        {
            get { return AppDataCache.Instance.Data.item; }
        }

        #endregion

        #endregion

        #region Constants

        #region Queries
        /// <summary>
        /// Returns all the rows and columns of the <c>item</c> table.
        /// </summary>
        private const string QRY_ADAPTER_SELECT = 
            @"SELECT * FROM item;";

        /// <summary>
        /// The <c>NpgsqlDataAdapter</c> insert query for the <c>item</c> table.
        /// </summary>
        private const string QRY_ADAPTER_INSERT = // HACK: Need to handle case where cust_id is already taken.
            @"INSERT INTO staff (item_no, item_code, type, description, unit_price, retail_price, qty_on_hand, remarks, acq_date)
              VALUES (@item_no, @item_code, @type, @description, @unit_price, @retail_price, @qty_on_hand, @remarks, @acq_date);";

        /// <summary>
        /// The <c>NpgsqlDataAdapter</c> update query for the <c>item</c> table.
        /// </summary>
        private const string QRY_ADAPTER_UPDATE =
            @"UPDATE item
              SET    item_code = @item_code,
                     type = @type,
                     description = @description,
                     unit_price = @unit_price,
                     retail_price = @retail_price,
                     qty_on_hand = @qty_on_hand,
                     remarks = @remarks,
                     acq_date = @acq_date
              WHERE  staff_id = @staff_id;";

        private const string QRY_ADAPTER_DELETE =
            @"DELETE FROM staff
              WHERE  item_no = @item_no;";

        #endregion

        #endregion

        #region Public Methods
        /// <summary>
        /// Adds one customer to the <c>customerDataTable</c> and commits changes to the database.
        /// </summary>
        /// <param name="transaction">The database transaction handling the update.</param>
        /// <remarks>Precondition: This method assumes that the changes to the <c>customerDataTable</c>
        /// instance is already validated.</remarks>
        /// <returns>The number of rows updated.</returns>
        public int UpdateItemTable(NpgsqlTransaction transaction)
        {
            m_dataAdapter.UpdateCommand.Transaction = transaction;
            m_dataAdapter.InsertCommand.Transaction = transaction;
            m_dataAdapter.DeleteCommand.Transaction = transaction;
            // TODO: Exception handling for UpdateStaffTable()
            return m_dataAdapter.Update(AppDataCache.Instance.Data.item);
        }

        #endregion

        #region Non-Public Methods

        private void InitializeDataAdapter()
        {
            var insCmd = new NpgsqlCommand(QRY_ADAPTER_INSERT, m_conn);
            insCmd.Parameters.Add("item_no", NpgsqlDbType.Integer, 10, "item_no");
            insCmd.Parameters.Add("item_code", NpgsqlDbType.Varchar, 30, "item_code");
            insCmd.Parameters.Add("type", NpgsqlDbType.Varchar, 50, "type");
            insCmd.Parameters.Add("description", NpgsqlDbType.Varchar, 100, "description");
            insCmd.Parameters.Add("unit_price", NpgsqlDbType.Numeric, 10, "unit_price");
            insCmd.Parameters.Add("retail_price", NpgsqlDbType.Numeric, 10, "retail_price");
            insCmd.Parameters.Add("qty_on_hand", NpgsqlDbType.Integer, 10, "qty_on_hand");
            insCmd.Parameters.Add("remarks", NpgsqlDbType.Varchar, 100, "remarks");
            insCmd.Parameters.Add("acq_date", NpgsqlDbType.Date, 50, "acq_date");

            m_dataAdapter.InsertCommand = insCmd;

            var updCmd = new NpgsqlCommand(QRY_ADAPTER_UPDATE, m_conn);
            updCmd.Parameters.Add("item_no", NpgsqlDbType.Integer, 10, "item_no");
            updCmd.Parameters.Add("item_code", NpgsqlDbType.Varchar, 30, "item_code");
            updCmd.Parameters.Add("type", NpgsqlDbType.Varchar, 50, "type");
            updCmd.Parameters.Add("description", NpgsqlDbType.Varchar, 100, "description");
            updCmd.Parameters.Add("unit_price", NpgsqlDbType.Numeric, 10, "unit_price");
            updCmd.Parameters.Add("retail_price", NpgsqlDbType.Numeric, 10, "retail_price");
            updCmd.Parameters.Add("qty_on_hand", NpgsqlDbType.Integer, 10, "qty_on_hand");
            updCmd.Parameters.Add("remarks", NpgsqlDbType.Varchar, 100, "remarks");
            updCmd.Parameters.Add("acq_date", NpgsqlDbType.Date, 50, "acq_date");

            m_dataAdapter.UpdateCommand = updCmd;

            var delCmd = new NpgsqlCommand(QRY_ADAPTER_DELETE, m_conn);
            delCmd.Parameters.Add("item_no", NpgsqlDbType.Integer, 10, "item_no");

            m_dataAdapter.DeleteCommand = delCmd;

            m_dataAdapter.TableMappings.Add("item", "item");
        }

        private void InitializeEventHandlers()
        {
            
        }

        private void ListenForNotifications()
        {
            if (m_conn.State != ConnectionState.Open)
                m_conn.Open();
            NpgsqlCommand listenCmd = 
                new NpgsqlCommand("LISTEN updated_item;", m_conn);
            listenCmd.ExecuteNonQuery();
            m_conn.Close();
        }

        #endregion
    }
}
